﻿Imports System.Data.SqlClient
Imports System.Drawing.Printing

Public Class Reception

    Private user As String
    Private userSite As String
    Private cn As CnData
    Private da As SqlDataAdapter
    Private ds As DataSet
    Private cmd As SqlCommand
    Private dtr As SqlDataReader
    Private reprise As Boolean

    Public Sub New(ByVal user As String, ByRef cn As CnData, ByVal userSite As String, ByVal chargement As String, ByVal reprise As Boolean)
        ' Cet appel est requis par le concepteur.
        InitializeComponent()

        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().
        Me.user = user
        Me.cn = cn
        Me.userSite = userSite
        Me.Chargement.Text = chargement
        ds = New DataSet
        da = New SqlDataAdapter
        Me.reprise = reprise
    End Sub

    Private Sub Reception_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'TODO: cette ligne de code charge les données dans la table 'LaisserPasserDS.BR'. Vous pouvez la déplacer ou la supprimer selon vos besoins.
        Me.BRTableAdapter.Fill(Me.LaisserPasserDS.BR, "")
        'TODO: cette ligne de code charge les données dans la table 'LaisserPasserDS.BRdétails'. Vous pouvez la déplacer ou la supprimer selon vos besoins.
        Me.BRdétailsTableAdapter.Fill(Me.LaisserPasserDS.BRdétails, "")
        cn.Open()
        ds.Tables.Add("Attendu")
        da = New SqlDataAdapter("SELECT     LP_Chargement.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique, LP.Statut AS [Statut agrès], 'Manquant' AS [Statut réception] " _
                    & " FROM  SpeLP RIGHT OUTER JOIN Flux RIGHT OUTER JOIN LP_Chargement INNER JOIN LP ON LP_Chargement.LP = LP.LP ON Flux.NumFlux = LP.Flux ON SpeLP.LP = LP_Chargement.LP " _
                    & "WHERE     (LP_Chargement.Chargement = '" & Chargement.Text & "') " _
                    & "GROUP BY LP_Chargement.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux, LP.Statut", cn.getCn)
        da.Fill(ds, "Attendu")
        ds.Tables.Add("Recu")
        ds.Tables.Add("Excedent")
        da = New SqlDataAdapter("SELECT     LP_Chargement.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique, 'Reçu' AS [Statut agrès], 'OK' AS [Statut réception] " _
                    & " FROM         Flux RIGHT OUTER JOIN " _
                    & "LP ON Flux.NumFlux = LP.Flux RIGHT OUTER JOIN " _
                    & "LP_Chargement LEFT OUTER JOIN " _
                    & "SpeLP ON LP_Chargement.LP = SpeLP.LP ON LP.LP = LP_Chargement.LP " _
                    & "WHERE     (LP_Chargement.Chargement = 'Vide') " _
                    & "GROUP BY LP_Chargement.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
        da.Fill(ds, "Recu")
        da = New SqlDataAdapter("SELECT     LP_Chargement.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique, 'Reçu' AS [Statut agrès], 'OK' AS [Statut réception] " _
                    & " FROM         Flux RIGHT OUTER JOIN " _
                    & "LP ON Flux.NumFlux = LP.Flux RIGHT OUTER JOIN " _
                    & "LP_Chargement LEFT OUTER JOIN " _
                    & "SpeLP ON LP_Chargement.LP = SpeLP.LP ON LP.LP = LP_Chargement.LP " _
                    & "WHERE     (LP_Chargement.Chargement = 'Vide') " _
                    & "GROUP BY LP_Chargement.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
        da.Fill(ds, "Excedent")
        ListeRecu.DataSource = ds.Tables("Attendu")
        ListeExcedent.DataSource = ds.Tables("Excedent")
        Cptatt.Text = ds.Tables("Attendu").Rows.Count
        CptRest.Text = ds.Tables("Attendu").Select("[Statut réception]<>'OK'").Length
        Cptscan.Text = ds.Tables("Attendu").Select("[Statut réception]='OK'").Length + ds.Tables("Excedent").Rows.Count

        If reprise Then
            Dim dtr1 As SqlDataReader
            Dim cmd1 As SqlCommand
            cmd1 = New SqlCommand("SELECT Distinct LP " _
                                        & " FROM BrouillonD " _
                                        & " WHERE     (Chargement = '" & Chargement.Text & "') order by lp desc", cn.getCn)
            dtr1 = cmd1.ExecuteReader

            Dim s As New ArrayList
            While dtr1.Read()
                s.Add(dtr1.Item("LP").ToString)
            End While
            dtr1.Close()
            cmd1.Dispose()
            For Each t As String In s
                If t <> "" Then scan.Text = t
                ajoutB()
                scan.Text = Nothing
            Next
        End If
        scan.Text = Nothing

        cn.Close()
        Me.ReportViewer1.RefreshReport()
    End Sub

    Private Sub ajoutB()
        If ds.Tables("Attendu").Select("[N° LP]='" & scan.Text & "'").Length > 0 Then
            ds.Tables("Attendu").Select("[N° LP]='" & scan.Text & "'")(0).Item("Statut réception") = "OK"
            ds.Tables("Attendu").Select("[N° LP]='" & scan.Text & "'")(0).Item("Statut agrès") = "Reçu"
            CptRest.Text = ds.Tables("Attendu").Select("[Statut réception]<>'OK'").Length
            Cptscan.Text = ds.Tables("Attendu").Select("[Statut réception]='OK'").Length + ds.Tables("Excedent").Rows.Count
        Else
            If ds.Tables("Excedent").Select("[N° LP]='" & scan.Text & "'").Length <= 0 Then
                If scan.Text.Length = 18 Then
                    If scan.Text.Substring(4, 2) <> userSite Then
                        da = New SqlDataAdapter("SELECT     LP.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique,  'Reçu' AS [Statut agrès], 'Non attendu' AS [Statut réception] " _
                            & " FROM         Flux RIGHT OUTER JOIN SpeLP RIGHT OUTER JOIN LP ON SpeLP.LP = LP.LP ON Flux.NumFlux = LP.Flux " _
                            & " WHERE     (LP.LP = '" & scan.Text & "') " _
                            & " GROUP BY LP.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
                    Else
                        da = New SqlDataAdapter("SELECT     LP.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique,  'Reçu' AS [Statut agrès], 'Excedent' AS [Statut réception] " _
                            & " FROM         Flux RIGHT OUTER JOIN SpeLP RIGHT OUTER JOIN LP ON SpeLP.LP = LP.LP ON Flux.NumFlux = LP.Flux " _
                            & " WHERE     (LP.LP = '" & scan.Text & "') " _
                            & " GROUP BY LP.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
                    End If
                Else
                    da = Nothing
                    da = New SqlDataAdapter("SELECT     LP.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique,  'Reçu' AS [Statut agrès], 'Excedent' AS [Statut réception] " _
                            & " FROM         Flux RIGHT OUTER JOIN SpeLP RIGHT OUTER JOIN LP ON SpeLP.LP = LP.LP ON Flux.NumFlux = LP.Flux " _
                            & " WHERE     (LP.LP = '" & scan.Text & "') " _
                            & " GROUP BY LP.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
                End If
                If da.Fill(ds, "Excedent") = 0 Then
                    cmd = New SqlCommand("INSERT INTO LP (LP,Expediteur,Destinataire,Flux,TypeAgres,NombreColis,DateImp,UtilImp,Statut) VALUES ('" & scan.Text & "','00','00','00','',1,'" & Date.Today & "','" & user & "','Créé')", cn.getCn)
                    cmd.ExecuteNonQuery()
                    ds.Tables("Excedent").Rows.Add(New String() {scan.Text, "", "0", "", "", "Reçu", "Excedent"})
                End If
                Cptscan.Text = ds.Tables("Attendu").Select("[Statut réception]='OK'").Length + ds.Tables("Excedent").Rows.Count

            End If
        End If

        scan.Text = Nothing
    End Sub

    Private Sub scan_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles scan.KeyDown
        If e.KeyCode = Keys.Enter Then
            If String.IsNullOrEmpty(scan.Text) Then
                scan.Text = Nothing
                MsgBox("Veuiller saisir le numéro de chargement")
                Exit Sub
            End If
            If Not IsNumeric(scan.Text) Then
                scan.Text = Nothing
                MsgBox("Veuillez verrouiller la touche MAJ")
                Exit Sub
            End If
            If ds.Tables("Attendu").Select("[N° LP]='" & scan.Text & "'").Length > 0 Then
                ds.Tables("Attendu").Select("[N° LP]='" & scan.Text & "'")(0).Item("Statut réception") = "OK"
                ds.Tables("Attendu").Select("[N° LP]='" & scan.Text & "'")(0).Item("Statut agrès") = "Reçu"
                CptRest.Text = ds.Tables("Attendu").Select("[Statut réception]<>'OK'").Length
                Cptscan.Text = ds.Tables("Attendu").Select("[Statut réception]='OK'").Length + ds.Tables("Excedent").Rows.Count
            Else
                If ds.Tables("Excedent").Select("[N° LP]='" & scan.Text & "'").Length <= 0 Then
                    cn.Open()
                    da = Nothing
                    If scan.Text.Length = 18 Then
                        If scan.Text.Substring(4, 2) <> userSite Then
                            da = New SqlDataAdapter("SELECT     LP.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique,  'Reçu' AS [Statut agrès], 'Non attendu' AS [Statut réception] " _
                                & " FROM         Flux RIGHT OUTER JOIN SpeLP RIGHT OUTER JOIN LP ON SpeLP.LP = LP.LP ON Flux.NumFlux = LP.Flux " _
                                & " WHERE     (LP.LP = '" & scan.Text & "') " _
                                & " GROUP BY LP.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
                        Else
                            da = New SqlDataAdapter("SELECT     LP.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique,  'Reçu' AS [Statut agrès], 'Excedent' AS [Statut réception] " _
                                & " FROM         Flux RIGHT OUTER JOIN SpeLP RIGHT OUTER JOIN LP ON SpeLP.LP = LP.LP ON Flux.NumFlux = LP.Flux " _
                                & " WHERE     (LP.LP = '" & scan.Text & "') " _
                                & " GROUP BY LP.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
                        End If
                    Else
                        da = New SqlDataAdapter("SELECT     LP.LP AS [N° LP], LP.TypeAgres AS [Type d'agrès], LP.NombreColis AS [Nb de colis], Flux.NomFlux AS [Type de flux], MAX(SpeLP.Spe) AS Spécifique,  'Reçu' AS [Statut agrès], 'Excedent' AS [Statut réception] " _
                                & " FROM         Flux RIGHT OUTER JOIN SpeLP RIGHT OUTER JOIN LP ON SpeLP.LP = LP.LP ON Flux.NumFlux = LP.Flux " _
                                & " WHERE     (LP.LP = '" & scan.Text & "') " _
                                & " GROUP BY LP.LP, LP.TypeAgres, LP.NombreColis, Flux.NomFlux", cn.getCn)
                    End If
                    If da.Fill(ds, "Excedent") = 0 Then
                        cmd = New SqlCommand("INSERT INTO LP (LP,Expediteur,Destinataire,Flux,TypeAgres,NombreColis,DateImp,UtilImp,Statut) VALUES ('" & scan.Text & "','00','00','00','',0,'" & Date.Today & "','" & user & "','Créé')", cn.getCn)
                        cmd.ExecuteNonQuery()
                        ds.Tables("Excedent").Rows.Add(New String() {scan.Text, "", "0", "", "", "Reçu", "Excedent"})
                    End If
                    cn.Close()
                    Cptscan.Text = ds.Tables("Attendu").Select("[Statut réception]='OK'").Length + ds.Tables("Excedent").Rows.Count

                End If
            End If

            scan.Text = Nothing
        End If

    End Sub


    Private Sub brouillon_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles brouillon.Click

        cn.Open()
        cmd = New SqlCommand("DELETE FROM BrouillonD " _
                                       & " WHERE     (Chargement = '" & Chargement.Text & "')", cn.getCn)
        cmd.ExecuteNonQuery()
        For Each dr As DataRow In ds.Tables("Attendu").Select("[Statut réception]='OK'")
            cmd = New SqlCommand("INSERT INTO BrouillonD (LP,chargement) VALUES ('" & dr.Item("N° LP") & "','" & Chargement.Text & "')", cn.getCn)
            cmd.ExecuteNonQuery()
        Next
        For Each dr As DataRow In ds.Tables("Excedent").Rows
            cmd = New SqlCommand("INSERT INTO BrouillonD (LP,chargement) VALUES ('" & dr.Item("N° LP") & "','" & Chargement.Text & "')", cn.getCn)
            cmd.ExecuteNonQuery()
            'cmd = New SqlCommand("UPDATE LP SET Statut = 'Reçu' WHERE LP = '" & dr.Item("N° LP") & "'", cn.getCn)
            'cmd.ExecuteNonQuery()
        Next
        cmd = New SqlCommand("UPDATE Chargements SET Statut = 'Déchargement' WHERE Chargement = '" & Chargement.Text & "'", cn.getCn)
        cmd.ExecuteNonQuery()

        cn.Close()
        MsgBox("Brouillon enregistré")
        Me.Close()
    End Sub


    Private Sub valider_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles valider.Click
        valider.Enabled = False
        cn.Open()
        Me.Cursor = Cursors.WaitCursor
        cmd = New SqlCommand("DELETE FROM BrouillonD " _
                                       & " WHERE     (Chargement = '" & Chargement.Text & "')", cn.getCn)
        cmd.ExecuteNonQuery()

        For Each dr As DataRow In ds.Tables("Attendu").Select("[Statut réception]='OK'")
            cmd = New SqlCommand("UPDATE LP_Chargement SET Statut = 'Reçu',Anomalie = 'OK' WHERE LP = '" & dr.Item("N° LP") & "' AND Chargement = '" & Chargement.Text & "'", cn.getCn)
            cmd.ExecuteNonQuery()
            cmd = New SqlCommand("UPDATE LP SET Statut = 'Reçu' WHERE LP = '" & dr.Item("N° LP") & "'", cn.getCn)
            cmd.ExecuteNonQuery()
        Next
        For Each dr As DataRow In ds.Tables("Attendu").Select("[Statut réception]='Manquant'")
            cmd = New SqlCommand("UPDATE LP_Chargement SET Statut = '" & dr.Item("Statut agrès") & "' ,Anomalie = 'Manquant' WHERE LP = '" & dr.Item("N° LP") & "' AND Chargement = '" & Chargement.Text & "'", cn.getCn)
            cmd.ExecuteNonQuery()
        Next
        For Each dr As DataRow In ds.Tables("Excedent").Rows
            cmd = New SqlCommand("INSERT INTO LP_Excedent (LP,Chargement,Anomalie) VALUES ('" & dr.Item("N° LP") & "','" & Chargement.Text & "','" & dr.Item("Statut réception") & "')", cn.getCn)
            cmd.ExecuteNonQuery()
            cmd = New SqlCommand("UPDATE LP SET Statut = 'Reçu' WHERE LP = '" & dr.Item("N° LP") & "'", cn.getCn)
            cmd.ExecuteNonQuery()
        Next
        If ds.Tables("Attendu").Select("[Statut réception]='Manquant'").Length > 0 Then
            cmd = New SqlCommand("UPDATE Chargements SET DateRec = '" & Date.Today & "' ,HeureRec = '" & FormatDateTime(Date.Now, DateFormat.LongTime) & "' , UtilRec = '" & user & "',Statut = 'Anomalie' WHERE Chargement = '" & Chargement.Text & "'", cn.getCn)
        Else
            cmd = New SqlCommand("UPDATE Chargements SET DateRec = '" & Date.Today & "' ,HeureRec = '" & FormatDateTime(Date.Now, DateFormat.LongTime) & "' , UtilRec = '" & user & "',Statut = 'Reçu' WHERE Chargement = '" & Chargement.Text & "'", cn.getCn)
        End If
        cmd.ExecuteNonQuery()
        If cmd.ExecuteNonQuery() > 0 Then
            Me.BRTableAdapter.ClearBeforeFill = True
            Me.BRdétailsTableAdapter.ClearBeforeFill = True

            'TODO: cette ligne de code charge les données dans la table 'LaisserPasserDataSet.BL'. Vous pouvez la déplacer ou la supprimer selon vos besoins.
            Me.BRTableAdapter.Fill(Me.LaisserPasserDS.BR, Chargement.Text)
            'TODO: cette ligne de code charge les données dans la table 'LaisserPasserDataSet.ListeLP'. Vous pouvez la déplacer ou la supprimer selon vos besoins.
            Me.BRdétailsTableAdapter.Fill(Me.LaisserPasserDS.BRdétails, Chargement.Text)
            'MsgBox(LaisserPasserDataSet11.BRdétails.Rows.Count)
            Dim print As New PageSettings
            print.Margins.Bottom = 20
            print.Margins.Top = 20
            print.Margins.Left = 35
            print.Margins.Right = 35
            print.PrinterSettings.Copies = 2

            Dim p As New Microsoft.Reporting.WinForms.ReportParameter("Duplicata", "False")
            Me.ReportViewer1.LocalReport.SetParameters(p)
            Me.ReportViewer1.SetPageSettings(print)
            Me.ReportViewer1.RefreshReport()


            While Not Me.ReportViewer1.CurrentStatus.CanPrint
                Application.DoEvents()
            End While
            ReportViewer1.PrintDialog(print.PrinterSettings)
            Me.Focus()
            MsgBox("Réception validée")
        Else
            MsgBox("Une erreur s'est produite lors de l'écriture des données, réception non enregistrée")
            valider.Enabled = True
            Exit Sub
        End If
        Me.Cursor = Cursors.Default
        ReportViewer1.Dispose()
        cn.Close()
        Me.Close()
    End Sub



    Private Sub ListeExcedent_RowsRemoved(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles ListeExcedent.RowsRemoved
        Cptscan.Text = ds.Tables("Attendu").Select("[Statut réception]='OK'").Length + ds.Tables("Excedent").Rows.Count
    End Sub
End Class